/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.daos.BscBroadcastResultDao;
import com.elcom.emocbc.dbmapping.tables.pojos.KpiBsc;
import com.elcom.emocbc.dbmapping.tables.pojos.KpiSmscbCenter;
import com.google.common.collect.Lists;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class BscBroadcastResultPersistance extends BscBroadcastResultDao {

    public BscBroadcastResultPersistance() throws SQLException {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }

    public List<KpiSmscbCenter> getSmscbCenterNormalKpi(Timestamp startDate, Timestamp endDate) throws SQLException {
        ResultSetHandler<List<KpiSmscbCenter>> resultSetHandler = new ResultSetHandler<List<KpiSmscbCenter>>() {
            @Override
            public List<KpiSmscbCenter> handle(ResultSet rs) throws SQLException {
                List<KpiSmscbCenter> result = Lists.newArrayList();
                while (rs.next()) {
                    int i = 1;
                    KpiSmscbCenter kpiSmscbCenter = new KpiSmscbCenter();
                    kpiSmscbCenter.setSmscbCenterId(rs.getString(i++));
                    kpiSmscbCenter.setSmscbCenterName(rs.getString(i++));
                    kpiSmscbCenter.setSuccessCells(rs.getInt(i++));
                    kpiSmscbCenter.setTotalCells(rs.getInt(i++));
                    kpiSmscbCenter.setKpi(BigDecimal.valueOf(rs.getDouble(i++)));
                    result.add(kpiSmscbCenter);
                }
                return result;
            }
        };
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT b.center, s.smscb_center_name, sum(success_cells), sum(total_cells), (sum(success_cells) / sum(total_cells)) * 100 "
                + " FROM bsc_broadcast_result br "
                + " INNER JOIN bsc b "
                + "	ON b.bsc_id = br.bsc_id "
                + " INNER JOIN smscb_center s "
                + "     ON s.smscb_center_id = b.center "
                + " WHERE br.broadcast_time BETWEEN ? AND ? "
                + " AND b.bsc_id NOT IN (SELECT bsc_id from RNC) "
                + " GROUP BY b.center, s.smscb_center_name "
                + " ORDER BY b.center, s.smscb_center_name ";
        try {
            return qr.query(con, sql, resultSetHandler, startDate, endDate);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }

    public List<KpiBsc> getBscNormalKpi(Timestamp startDate, Timestamp endDate) throws SQLException {
        ResultSetHandler<List<KpiBsc>> resultSetHandler = new ResultSetHandler<List<KpiBsc>>() {
            @Override
            public List<KpiBsc> handle(ResultSet rs) throws SQLException {
                List<KpiBsc> result = Lists.newArrayList();
                while (rs.next()) {
                    int i = 1;
                    KpiBsc kpiBsc = new KpiBsc();
                    kpiBsc.setBscId(rs.getString(i++));
                    kpiBsc.setSuccessCells(rs.getInt(i++));
                    kpiBsc.setTotalCells(rs.getInt(i++));
                    kpiBsc.setKpi(BigDecimal.valueOf(rs.getDouble(i++)));
                    result.add(kpiBsc);
                }
                return result;
            }
        };
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT br.bsc_id, sum(success_cells), sum(total_cells), (sum(success_cells) / sum(total_cells)) * 100 "
                + " FROM bsc_broadcast_result br "
                + " WHERE br.broadcast_time BETWEEN ? AND ? "
                + " GROUP BY br.bsc_id "
                + " ORDER BY br.bsc_id ";
        try {
            return qr.query(con, sql, resultSetHandler, startDate, endDate);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }
    
    public List<KpiBsc> getBscNormalKpi(Timestamp startDate, Timestamp endDate, String smscbCenterId) throws SQLException {
        ResultSetHandler<List<KpiBsc>> resultSetHandler = new ResultSetHandler<List<KpiBsc>>() {
            @Override
            public List<KpiBsc> handle(ResultSet rs) throws SQLException {
                List<KpiBsc> result = Lists.newArrayList();
                while (rs.next()) {
                    int i = 1;
                    KpiBsc kpiBsc = new KpiBsc();
                    kpiBsc.setBscId(rs.getString(i++));
                    kpiBsc.setSuccessCells(rs.getInt(i++));
                    kpiBsc.setTotalCells(rs.getInt(i++));
                    kpiBsc.setKpi(BigDecimal.valueOf(rs.getDouble(i++)));
                    result.add(kpiBsc);
                }
                return result;
            }
        };
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT br.bsc_id, sum(success_cells), sum(total_cells), (sum(success_cells) / sum(total_cells)) * 100 "
                + " FROM bsc_broadcast_result br "
                + " INNER JOIN bsc b "
                + "	ON b.bsc_id = br.bsc_id "
                + " WHERE br.broadcast_time BETWEEN ? AND ? "
                + " AND b.center = ?"
                + " GROUP BY br.bsc_id "
                + " ORDER BY br.bsc_id ";
        try {
            return qr.query(con, sql, resultSetHandler, startDate, endDate, smscbCenterId);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }
}
